package com.iciot.dao.impl;

import com.iciot.dao.UserDao;
import com.iciot.domain.User;
import com.iciot.utils.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;

public class UserDaoImpl implements UserDao {
    @Override
    public void insert(User user) throws Exception {
        String sql = "insert into user (username, password) values (?, ?)";
        Connection con = JDBCUtils.getCon();
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,user.getUname());
        ps.setString(2,user.getUpwd());
        ps.executeUpdate();
        JDBCUtils.close(con,ps,null);
    }

    @Override
    public void delete(int id) throws Exception {
        String sql="delete from user where id=?";
        Connection con = JDBCUtils.getCon();
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1,id);
        ps.executeUpdate();
        JDBCUtils.close(con,ps,null);
    }

    @Override
    public void update(User user) throws SQLException {
        String sql = "update user set username=?,password=? where id=?";
        Connection con = JDBCUtils.getCon();
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,user.getUname());
        ps.setString(2,user.getUpwd());
        ps.setInt(3,user.getUid());
        ps.executeUpdate();
        JDBCUtils.close(con,ps,null);
    }

    @Override
    public User findById(int id) throws Exception {
        String sql = "select * from user where id = ?";
        Connection con = JDBCUtils.getCon();
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setInt(1,id);
        ResultSet rs = ps.executeQuery();
        User user = new User();
        while (rs.next()){
            user.setUid(rs.getInt(1));
            user.setUname(rs.getString(2));
            user.setUpwd(rs.getString(3));
        }
        JDBCUtils.close(con,ps,rs);
        return user;
    }

    @Override
    public User findByName(String name) throws Exception{
        String sql = "select * from user where username = ?";
        Connection con = JDBCUtils.getCon();
        PreparedStatement ps = con.prepareStatement(sql);
        ps.setString(1,name);
        ResultSet rs = ps.executeQuery();
        User user = new User();
        while (rs.next()){
            user.setUid(rs.getInt(1));
            user.setUname(rs.getString(2));
            user.setUpwd(rs.getString(3));
        }
        JDBCUtils.close(con,ps,rs);
        return user;
    }

    @Override
    public ArrayList<User> findAll() throws Exception{
        ArrayList<User> users = new ArrayList<>();
        String sql = "select * from user";
        Connection con = JDBCUtils.getCon();
        PreparedStatement ps = con.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();

        while (rs.next()){
            User user = new User();
            int id = rs.getInt("id");
            String name = rs.getString("username");
            String pwd = rs.getString("password");
            users.add(new User(id,name,pwd));
        }
        JDBCUtils.close(con,ps,rs);
        return users;
    }
}
